package beans;
import com.sun.mail.iap.Response;
import java.io.File;
import java.io.PrintWriter;
import java.io.Serializable;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.StringTokenizer;
import org.apache.commons.fileupload.FileItem;
import util.ConnectionManager;
import util.SystemException;
import util.Utilities;
import org.apache.commons.fileupload.DiskFileUpload;
import org.apache.commons.fileupload.FileItem;

import javax.servlet.*;
import javax.servlet.http.*;
/**
 *
 * @author HOME
 */
public class ModelBean implements Serializable{
    private String itemID;
    
    private String name;
    
    private String type;
    
    private String company;
    
    private String scale;
    
    private String price;
    
    private String description;
    
    private String imageURL;
    
    private int quantity;
    
    private String distributionDate;
    
    private ModelBean(String itemID, String name, String type, String company, String scale,
            String price, String description, String imageURL, int quantity, String distributionDate) {
        this.itemID = itemID;
        this.name = name;
        this.type = type;
        this.company = company;
        this.scale = scale;
        this.price = price;
        this.description = description;
        this.imageURL = imageURL;
        this.setQuantity(quantity);
        this.distributionDate = distributionDate;
    }
    
    private ModelBean() {
        
    }
    
    public static ModelBean[] getAllModel() throws SystemException {
        return model("SELECT * FROM model ORDER BY itemID;");
    }
    
    public static ModelBean[] getAllModelByImageURL(final String imageURL) throws SystemException {
        return model("SELECT * FROM model WHERE imageURL='"+imageURL+"';");
    }
    
    public static ModelBean getModelByItemID(final String itemID) throws SystemException {
        ModelBean[] bs = model("SELECT * FROM model WHERE itemID="+itemID+";");
        if(bs.length == 1) {
            return bs[0];
        }else {
            return null;
        }
    }
    
    public static int getTotalRecordCount() throws SystemException {
        ResultSet rs = null;
        Statement stmt = null;
        Connection con = null;
        
        try {
            con = ConnectionManager.connect();
            String query = "select count(itemID) as no from model";
            stmt = con.createStatement();
            rs = stmt.executeQuery(query);
            
            while(rs.next()) {
                int count = rs.getInt("no");
                return count;
            }
            
        }catch (Exception e) {
            throw new SystemException("Database Service is not availiable for a moment.");
        }finally {
            Utilities.close(rs, stmt, con);
        }
        return 0;
    }
    
    public static int getTotalRecordCountImage() throws SystemException {
        ResultSet rs = null;
        Statement stmt = null;
        Connection con = null;
        
        try {
            con = ConnectionManager.connect();
            String query = "select count(imageURL) as no from model";
            stmt = con.createStatement();
            rs = stmt.executeQuery(query);
            
            while(rs.next()) {
                int count = rs.getInt("no");
                return count;
            }
            
        }catch (Exception e) {
            throw new SystemException("Database Service is not availiable for a moment.");
        }finally {
            Utilities.close(rs, stmt, con);
        }
        return 0;
    }
    
    public static ModelBean[] getOutOfStock() throws SystemException {
        return model("SELECT * FROM model where quantity3 <= 0 ORDER BY itemID;");
    }
    
    public static ModelBean[] advanceSearch(String keyword, String scale, String company, String type, String miniPrice,
            String maxPrice, String startDate, String endDate) throws SystemException {
        
        StringBuilder sql = new StringBuilder();
        sql.append("SELECT * FROM model WHERE true ");
        if (!scale.equals("*")) {
            sql.append(" AND scale='"+scale+"'");
        }
        if (!company.equals("*")) {
            sql.append(" AND company='"+company+"'");
        }
        if (!type.equals("*")) {
            sql.append(" AND type='"+type+"'");
        }
        
        double dMiniPrice = 0.0;
        try {
            dMiniPrice = Double.parseDouble(miniPrice);
        } catch (NumberFormatException ex) {
        }
        
        double dMaxPrice = 0.0;
        try {
            dMaxPrice = Double.parseDouble(maxPrice);
        } catch (NumberFormatException ex) {
        }
        
        if (!(dMiniPrice == 0.0 && dMaxPrice == 0.0)) {
            if(dMiniPrice <= dMaxPrice) {
                sql.append(" AND price BETWEEN "+dMiniPrice+" AND "+dMaxPrice+"");
            }
        }
        
        if(!(startDate.equals("Any") && endDate.equals("Any"))) {
            sql.append(" AND date BETWEEN '"+startDate+"' AND '"+endDate+"'");
        }
        if (!"".equals(keyword)) {
            sql.append(" AND (name LIKE '%"+keyword+"%' OR description LIKE '%"+keyword+"%' OR itemID LIKE '%"+keyword+"%')" );
        }
        sql.append(";");
        System.out.println(sql);
        
        return model(sql.toString());
    }
    
    
    public static ModelBean[] byKeyword(String keyword) throws SystemException {
        
        return model("SELECT * FROM model WHERE name LIKE '%"+keyword+"%' OR type LIKE '%"+keyword+"%'"+
                "OR itemID LIKE '%"+keyword+"%' OR scale LIKE '%"+keyword+"%' OR company LIKE '%"+keyword+"%' OR description LIKE '%"+keyword+"%' ORDER BY itemID");
    }
    
    public static ModelBean[] byCompany(String company) throws SystemException {
        return model("SELECT * FROM model WHERE company LIKE '%"+company+"%'ORDER BY itemID");
    }
    
    public static ModelBean[] byScale(String scale) throws SystemException {
        String query = "SELECT * FROM model WHERE scale LIKE '%"+scale+"%'ORDER BY itemID";
        return model(query);
    }
    
    public static ModelBean[] byPrice(String miniPrice, String maxPrice) throws SystemException {
        return model("SELECT * FROM model WHERE price BETWEEN '"+miniPrice+"'AND'"+maxPrice+"'");
    }
    
    public static ModelBean[] byDate(String startDate, String endDate) throws SystemException {
        return model("SELECT * FROM model WHERE date BETWEEN '"+startDate+"'AND'"+endDate+"'");
    }
    
    public static ModelBean[] byType(String type) throws SystemException {
        return model("select * from model where type ='"+type+"' ORDER BY itemID");
    }
    
    public static int create(final String id, final String fileName1, final String name, final String description, final String price, final String scale, final String company, final String type, final String quantity) throws SQLException, SystemException {
        
        Connection con = null;
        ResultSet rs = null;
        PreparedStatement update = null;
        int itemID = 0;
        int result =0;
        try {
            con = ConnectionManager.connect();
            
            update = con.prepareStatement("insert into model  (name, type, company, scale, price, description, imageURL, quantity3, distributionDate) values (?, ?, ?, ?, ?, ?, ?, ?, date())");
            
            String imageURL = fileName1;
            //update.setString(1, id);
            update.setString(1, name);
            update.setString(2, type);
            update.setString(3, company);
            update.setString(4, scale);
            update.setString(5, price);
            update.setString(6, description);
            update.setString(7, imageURL);
            update.setString(8, quantity);
            result = update.executeUpdate();
        }catch (Exception e) {
            e.printStackTrace();
            throw new SystemException("Database Service is not availiable for a moment.");
        }finally {
            Utilities.close(null, update, con);
        }
        
        try {
            con = ConnectionManager.connect();
            
            if(result == 1) {
                //String query="select itemID from model where name='"+name+"' AND type='"+type+"' AND company='"+company+"' AND scale='"+scale+"' AND price="+price;
                String query = "select max(itemID) as maxID from model";
                rs = con.createStatement().executeQuery(query);
                rs.next();
                itemID = rs.getInt("maxID");
            } else {
                return -1;
            }
        }catch (Exception e) {
            e.printStackTrace();
            throw new SystemException("Database Service is not availiable for a moment.");
        }finally {
            Utilities.close(null, null, con);
        }
        return itemID;
    }
    
    public static int insertImageLink(final String id, final String fileName1) throws SQLException, SystemException {
        
        Connection con = null;
        PreparedStatement update = null;
        int result = 0;
        
        try {
            con = ConnectionManager.connect();
            
            update = con.prepareStatement("insert into image (itemID, imageURL) values (?, ?)");
            
            String imageURL = fileName1;
            update.setString(1, id);
            update.setString(2, imageURL);
            result = update.executeUpdate();
        }catch (Exception e) {
            e.printStackTrace();
            throw new SystemException("Database Service is not availiable for a moment.");
        }finally {
            Utilities.close(null, update, con);
        }
        return result;
    }
    
     public static int updateImageLink(final String imageID, final String fileName1) throws SQLException, SystemException {
        
        Connection con = null;
        PreparedStatement update = null;
        int result = 0;
        
        try {
            con = ConnectionManager.connect();
            
            update = con.prepareStatement("update image set imageURL =? where imageID=?");
            
            String imageURL = fileName1;
            update.setString(1, imageURL);
            update.setString(2, imageID);
            result = update.executeUpdate();
        }catch (Exception e) {
            e.printStackTrace();
            throw new SystemException("Database Service is not availiable for a moment.");
        }finally {
            Utilities.close(null, update, con);
        }
        return result;
    }
    
    static ModelBean[] model(final String query) throws SystemException {
        ResultSet rs = null;
        Statement stmt = null;
        Connection con = null;
        ModelBean[] finalResult = new ModelBean[0];
        try {
            con = ConnectionManager.connect();
            
            stmt = con.createStatement();
            rs = stmt.executeQuery(query);
            ArrayList<ModelBean> result = new ArrayList<ModelBean>();
            while(rs.next()) {
                ModelBean product = oneSetOfResultSetToItem(rs);
                result.add(product);
            }
            finalResult = (ModelBean[])result.toArray(finalResult);
        }catch (Exception e) {
            throw new SystemException("Database Service is not availiable for a moment.");
        }finally {
            Utilities.close(rs, stmt, con);
        }
        return finalResult;
    }
    
    public static String[] imageURL(final String query) throws SystemException {
        ResultSet rs = null;
        Statement stmt = null;
        Connection con = null;
        String[] finalResult = new String[0];
        try {
            con = ConnectionManager.connect();
            
            stmt = con.createStatement();
            rs = stmt.executeQuery(query);
            ArrayList<String> result = new ArrayList<String>();
            while(rs.next()) {
                String imageURL = rs.getString("imageURL");
                result.add(imageURL);
            }
            finalResult = (String[])result.toArray(finalResult);
        }catch (Exception e) {
            e.printStackTrace();
        }finally {
            Utilities.close(rs, stmt, con);
        }
        return finalResult;
    }
    
    public static String[] imageID(final String query) throws SystemException {
        ResultSet rs = null;
        Statement stmt = null;
        Connection con = null;
        String[] finalResult = new String[0];
        try {
            con = ConnectionManager.connect();
            
            stmt = con.createStatement();
            rs = stmt.executeQuery(query);
            ArrayList<String> result = new ArrayList<String>();
            while(rs.next()) {
                String imageID = Integer.toString(rs.getInt("imageID"));
                result.add(imageID);
            }
            finalResult = (String[])result.toArray(finalResult);
        }catch (Exception e) {
            e.printStackTrace();
        }finally {
            Utilities.close(rs, stmt, con);
        }
        return finalResult;
    }
    
    public static int getNewItemID() {
        Connection con = null;
        ResultSet rs = null;
        Statement stmt = null;
        int itemID = 0;
        try {
            con = ConnectionManager.connect();
            String query = "SELECT Max(itemID) as max FROM model";
            stmt = con.createStatement();
            rs = stmt.executeQuery(query);
            if (!rs.next()) {
                itemID = 1;
            } else {
                itemID = rs.getInt("max")+1;
            }
            
        }catch (SQLException e) {
            e.printStackTrace();
        } finally {
            Utilities.close(rs, stmt, con);
        }
        return itemID;
    }
    public static ModelBean oneSetOfResultSetToItem(final ResultSet rs) throws SQLException {
        String itemId = rs.getString("itemID");
        String name = rs.getString("name");
        String type = rs.getString("type");
        String company = rs.getString("company");
        String scale = rs.getString("scale");
        String price = rs.getString("price");
        String description = rs.getString("description");
        String imageURL = rs.getString("imageURL");
        int quantity = rs.getInt("quantity3");
        String distributionDate = rs.getString("distributionDate");
        ModelBean product = new ModelBean(itemId, name, type, company, scale, price, description, imageURL, quantity, distributionDate);
        return product;
    }
    
    public static void deleteModel(final String itemID) {
        
        Connection con = null;
        try {
            con = ConnectionManager.connect();
            PreparedStatement update = con.prepareStatement("DELETE FROM model WHERE itemID=?;");
            
            update.setString(1, itemID);
            update.execute();
            con.commit();
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            Utilities.close(con);
        }
    }
    
    public static int updateModel(String quantity, String name, String type, String company, String scale, String price, String distributionDate, String imageURL, String description, int itemID) {
        Statement stmt = null;
        Connection con = null;
        ModelBean result = null;
        int updateResult = -1;
        try {
            con = ConnectionManager.connect();
            
            String query = ("UPDATE model SET quantity3="+quantity+", name='"+name+"', type='"+type+"', company='"+company+"', scale='"+scale+"'," +
                    "price="+price+", imageURL='"+imageURL+"', distributionDate='"+distributionDate+"', description='"+description+"' WHERE itemID ="+itemID+";");
            stmt = con.createStatement();
            int rs = stmt.executeUpdate(query);
            con.commit();
            updateResult = 1;
            //result = oneSetOfResultSetToItem(rs);
            
            
        } catch (SQLException e) {
            e.printStackTrace();
            
        } finally {
            Utilities.close(stmt, con);
        }
        return updateResult;
    }
    public static String uploadImage(FileItem fi) {
        String fileName1 = null;
        boolean ok = false;
        String name1 = fi.getName();
        name1 = name1.replace('\\', '/' );
        StringTokenizer token = new StringTokenizer(name1, "/");
        while (token.hasMoreTokens()) {
            fileName1 = token.nextToken();
        }
        String fileName2 = fileName1.toLowerCase();
        if (fileName2.endsWith("jpg") ||
                fileName2.endsWith("gif") ||
                fileName2.endsWith("jpeg")) {
            
            File f = new File("C:\\Program Files\\Apache Software Foundation\\Tomcat 5.5a\\webapps\\image", fileName1);
            try {
                
                fi.write(f);
            } catch (Exception ex) {
                ex.printStackTrace();
            }
            ok = true;
        } else {
            ok = false;
        }
        return fileName1;
    }
    
    public static void updatePrice(String price, String itemID) {
        Statement stmt = null;
        Connection con = null;
        ModelBean result = null;
        try {
            con = ConnectionManager.connect();
            
            String query = ("UPDATE model SET price ='"+price+"' WHERE itemID ='"+itemID+"';");
            stmt = con.createStatement();
            int rs = stmt.executeUpdate(query);
            con.commit();
            //result = oneSetOfResultSetToItem(rs);
            
            
        } catch (SQLException e) {
            e.printStackTrace();
            
        } finally {
            Utilities.close(stmt, con);
        }
    }
    
    public static String[] getImages(final String itemID) throws SystemException {
        return imageURL(
                "SELECT image.imageURL AS imageURL "+
                "FROM image, model "+
                "WHERE "+itemID+" = image.itemID AND image.itemID = model.itemID");
    }
    
    public static String[] getImageIDs(final String itemID) throws SystemException {
        return imageID(
                "SELECT image.imageID AS imageID "+
                "FROM image, model "+
                "WHERE "+itemID+" = image.itemID AND image.itemID = model.itemID");
    }
    
    public String getItemID() {
        return itemID;
    }
    
    public String getName() {
        return name;
    }
    
    public void setName(String name) {
        this.name = name;
    }
    
    public String getType() {
        return type;
    }
    
    public void setType(String type) {
        this.type = type;
    }
    
    public String getCompany() {
        return company;
    }
    
    public void setCompany(String company) {
        this.company = company;
    }
    
    public String getScale() {
        return scale;
    }
    
    public void setScale(String scale) {
        this.scale = scale;
    }
    
    public String getPrice() {
        return price;
    }
    
    public void setPrice(String price) {
        this.price = price;
    }
    
    public String getDescription() {
        return description;
    }
    
    public void setDescription(String description) {
        this.description = description;
    }
    
    public String getImageURL() {
        return imageURL;
    }
    
    public void setImageURL(String imageURL) {
        this.imageURL = imageURL;
    }
    
    public int getQuantity() {
        return quantity;
    }
    
    public void setQuantity(int quantity) {
        this.quantity = quantity;
    }
    
    public String getDistributionDate() {
        return distributionDate;
    }
    
}
